﻿Imports System.Data.OleDb
Partial Public Class Rate
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("mobilestandardsdb").ConnectionString)
        oleDbConn.Open()
        If IsPostBack() Then

            ratePaper(oleDbConn, User.Identity.Name, Rating1.CurrentRating, Session("articleId"))
        Else
            Session("articleId") = Request.Params("ID")

        End If

        Rating1.CurrentRating = getUserRating(oleDbConn, Session("articleId"), User.Identity.Name)
        Rating2.CurrentRating = getAverageUserRating(oleDbConn, Session("articleId"))





        ', show the paper and the comments

        Dim paperId = Session("articleId")


        Dim relevantPaperStr As String = "SELECT * FROM papers WHERE [ID]=@ID"

        Dim paperDbCmd As New OleDbCommand(relevantPaperStr, oleDbConn)

        paperDbCmd.Parameters.AddWithValue("@ID", paperId)

        Dim paperDbRead = paperDbCmd.ExecuteReader()

        If paperDbRead.HasRows() Then
            paperDbRead.Read()
            lblauthor.Text = paperDbRead("Author")
            lblTitle.Text = paperDbRead("Title")
            lblAbstract.Text = paperDbRead("Abstract")
        End If

        paperDbRead.Close()
        paperDbCmd.Dispose()





    End Sub

    Protected Sub ratePaper(ByVal dbc As OleDbConnection, ByVal userName As String, ByVal score As Integer, ByVal paperId As Integer)
        Dim deletePrevOpinionSQL As String
        deletePrevOpinionSQL = "DELETE * FROM UserPaperRating WHERE User = @User AND fk_ID_papers = @fk_ID_papers"
        Dim deletePrevOpinionCmd As New OleDbCommand(deletePrevOpinionSQL, dbc)
        deletePrevOpinionCmd.Parameters.AddWithValue("@User", userName)
        deletePrevOpinionCmd.Parameters.AddWithValue("@fk_ID_papers", paperId)
        deletePrevOpinionCmd.ExecuteNonQuery()

        Dim addOpinionSQL = "INSERT INTO UserPaperRating([User],[fk_ID_papers],[Rating]) VALUES(@User,@fk_ID_papers,@Rating)"
        Dim addOpinionCmd As New OleDbCommand(addOpinionSQL, dbc)
        addOpinionCmd.Parameters.AddWithValue("@User", userName)
        addOpinionCmd.Parameters.AddWithValue("@fk_ID_papers", paperId)
        addOpinionCmd.Parameters.AddWithValue("@Rating", score)
        addOpinionCmd.ExecuteNonQuery()



    End Sub

    Protected Function getAverageUserRating(ByVal dbc As OleDbConnection, ByVal paperId As Integer)
        Dim averageSql As String = "SELECT AVG(Rating)as RatingAverage FROM UserPaperRating WHERE fk_ID_papers = " & paperId
        Dim averageCmd As New OleDbCommand(averageSql, dbc)
        Dim averageDbRead = averageCmd.ExecuteReader()
        averageDbRead.Read()
        Try
            Return CInt(averageDbRead("RatingAverage"))
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Protected Function getUserRating(ByVal dbc As OleDbConnection, ByVal paperId As Integer, ByVal userName As String)
        Dim userSql As String = "SELECT * FROM UserPaperRating WHERE fk_ID_papers=" & paperId & " AND [User]='" & userName & "'"
        Dim userRatingCmd As New OleDbCommand(userSql, dbc)
        Dim userRatingReader = userRatingCmd.ExecuteReader()
        userRatingReader.Read()
        Try
            Return CInt(userRatingReader("Rating"))
        Catch
            Return 0
        End Try
    End Function
End Class